﻿using System.Data;
using System.Data.SqlClient;
using System.Text;
using Microsoft.ApplicationBlocks.Data;
using System.Configuration;
using System;

namespace Customs.Components.GC
{
	/// <summary>
	/// Nguyên phụ liệu.
	/// </summary>
	public class NguyenPhuLieu
	{
		public static DataSet GetDanhSach(string maHaiQuan, string maDoanhNghiep)
		{
			DataSet ds;
            ds = GetDanhSachNPL(maHaiQuan, maDoanhNghiep);						
			return ds;			
		}		
		
		
		public static DataSet GetDanhSachNPL(string maHaiQuan, string maDoanhNghiep)
		{
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_SLXNK"].ConnectionString;
            StringBuilder query = new StringBuilder();
			query.Append("SELECT ");
			query.Append("NPL.So_HD			AS SoHopDong, ");
			query.Append("NPL.Ma_HQHD		AS MaHaiQuan, ");
			query.Append("NPL.DVGC			AS MaDoanhNghiep, ");
			query.Append("NPL.Ngay_Ky		AS NgayKy, ");
			query.Append("NPL.P_Code		AS Ma, ");
			query.Append("NPL.HS_Code		AS MaHS, ");
			query.Append("NPL.Ten_NPL		AS Ten, ");
			query.Append("NPL.Ma_DVT AS		DVT_ID, ");
			query.Append("NPL.SL_DK AS		SoLuongDangKy ");
			query.Append("FROM DNPLHD NPL ");
			query.Append("WHERE ");
			query.Append("NPL.Ma_HQHD = @MaHaiQuan AND ");
			query.Append("NPL.DVGC = @MaDoanhNghiep ");

			SqlParameter[] parameters = new SqlParameter[2];

			parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
			parameters[0].Value = maHaiQuan;

			parameters[1] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar, 14);
			parameters[1].Value = maDoanhNghiep;

            return SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
		}

        public static DataSet GetDanhSachNPLOfHopDong(string maHaiQuan, string maDoanhNghiep, string sohopdong, DateTime ngayky)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_SLXNK"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("NPL.So_HD			AS SoHopDong, ");
            query.Append("NPL.Ma_HQHD		AS MaHaiQuan, ");
            query.Append("NPL.DVGC			AS MaDoanhNghiep, ");
            query.Append("NPL.Ngay_Ky		AS NgayKy, ");
            query.Append("NPL.P_Code		AS Ma, ");
            query.Append("NPL.HS_Code		AS MaHS, ");
            query.Append("NPL.Ten_NPL		AS Ten, ");
            query.Append("NPL.Ma_DVT AS		DVT_ID, ");
            query.Append("NPL.SL_DK AS		SoLuongDangKy ");
            query.Append("FROM DNPLHD NPL ");
            query.Append("WHERE ");
            query.Append("NPL.Ma_HQHD = @MaHaiQuan AND ");
            query.Append("NPL.DVGC = @MaDoanhNghiep AND ");
            query.Append("NPL.So_HD = @So_HD AND ");
            query.Append("NPL.Ngay_Ky = @Ngay_Ky ");
            SqlParameter[] parameters = new SqlParameter[4];

            parameters[0] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[0].Value = maHaiQuan;

            parameters[1] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar, 14);
            parameters[1].Value = maDoanhNghiep;

            parameters[2] = new SqlParameter("@So_HD", SqlDbType.VarChar);
            parameters[2].Value = sohopdong;

            parameters[3] = new SqlParameter("@Ngay_Ky", SqlDbType.DateTime);
            parameters[3].Value = ngayky;

            return SqlHelper.ExecuteDataset(connectionString, CommandType.Text, query.ToString(), parameters);
        }
		
	}
}
